# 行级锁

# 1. 行锁的概念

1) 行锁(Record Lock):锁定单个行记录的锁,防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持
2) 间隙锁(Gap Lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。
3) 临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。 在RR隔离级别下支持。
跟表锁一样,行锁也分为以下两种类型
    共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排它锁,但是允许其他事务获得相同数据集的共享锁

    排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他锁。
1
2
3
4
5
6
7

常见的SQL语句,在执行时,所加的行锁如下:

SQL 行锁类型 说明
INSERT … 排他锁 自动加锁
UPDATE … 排他锁 自动加锁
DELETE … 排他锁 自动加锁
SELECT(正常) 不加任何锁
SELECT … LOCK IN SHARE MODE 共享锁 需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT … FOR UPDATE 排他锁 需要手动在SELECT之后加FOR UPDATE
SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE ,这两种sql其实就和普通的select语句功能是一样的,
引进这两种形式的原因是因为普通的select语句是不加锁的,但是我们在有些业务场景需要针对select语句加锁,
因此就可以通过SELECT … LOCK IN SHARE MODE为普通的select语句加上共享锁,或者通过SELECT … FOR UPDATE 为普通的sql语句加上排他锁。
1
2
3

# 2. 行级锁的实现方式

1) 在不通过索引条件查询的时候,InnoDB使用的是表锁,而不是行锁。
2) 由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以即使是访问不同行的记录,如果使用了相同的索引键,也是会出现锁冲突的。
3) 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
4) 即便在条件中使用了索引字段,但具体是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。


隐式加锁:
    A. InnoDB自动加意向锁。
    B. 对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);
    C. 对于普通SELECT语句,InnoDB不会加任何锁;

显示加锁:
    A. 共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
    B. 排他锁(X) :SELECT * FROM table_name WHERE ... FOR UPDATE


用SELECT … IN SHARE MODE获得共享锁,主要用在需要数据依存关系时来确认某行记录是否存在,并确保没有人对这个记录进行UPDATE或者DELETE操作。

但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁,对于锁定行记录后需要进行更新操作的应用,应该使用SELECT… FOR UPDATE方式获得排他锁。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 3. 行级锁使用case

  • session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 添加共享锁
mysql> select * from course where id = 1 lock in share mode;
+----+------+
| id | name |
+----+------+
|  1 | Java |
+----+------+
1 row in set (0.00 sec)


-- 添加排他锁
mysql> update course set  name='C++' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
  • session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from course where id = 1 lock in share mode;
+----+------+
| id | name |
+----+------+
|  1 | Java |
+----+------+
1 row in set (0.00 sec)

-- 可以看到多个共享锁并不互斥
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| my_test       | course      | NULL       | TABLE     | IS            | NULL      |
| my_test       | course      | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
| my_test       | course      | NULL       | TABLE     | IS            | NULL      |
| my_test       | course      | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)


-- 获取排他锁失败,session1 释放排他锁之后才可以正常update
mysql> update course set name = "C#" where id =  1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- 查看当前锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| my_test       | course      | NULL       | TABLE     | IX            | NULL      |
| my_test       | course      | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1         |    /* 行排他锁*/
| my_test       | course      | NULL       | TABLE     | IX            | NULL      |
| my_test       | course      | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)


-- 如果不根据索引去查找行,行锁会升级为表锁
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
# 3.1 间隙锁 & 临间锁(记录和间隙/行级锁和间隙锁的组合)
1) 在默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用的是临键锁进行搜索和索引扫描而并非使用行锁,这样做的目的是防止幻读,但是在特定情况下,临键锁会变化成行锁或间隙锁:
2) 当我们针对唯一索引对已存在的记录进行等值匹配的时候,临键锁会自动优化成行锁
3) 索引上的等值查询(唯一索引),给不存在的记录加锁时, 优化为间隙锁
4) 索引上的范围查询(唯一索引),会访问到不满足条件的第一个值为止。
1
2
3
4
  • session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from course;
+----+--------+
| id | name   |
+----+--------+
|  1 | C++    |
|  2 | PHP    |
|  3 | MySQL  |
|  4 | Golang |
| 15 | Python |
+----+--------+
5 rows in set (0.00 sec)

-- 不存在的索引加上排他锁就会升级为间隙锁
mysql> update course set name = 'Pgsql' where id = 10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
  • session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+-----------+
| my_test       | course      | NULL       | TABLE     | IX        | NULL      |
| my_test       | course      | PRIMARY    | RECORD    | X,GAP     | 15        |       /* 间隙锁,锁住了4-14这个间隙*/
+---------------+-------------+------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
Last Updated: 5/5/2023, 2:33:10 AM